package com.hrbxlh.util;

import com.google.common.collect.Lists;
import com.hrbxlh.vo.*;
import com.hrbxlh.util.*;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class ExcelsUtil {


    private ExcelsUtil() {
    }

//    public static <T> List<T> readExcelData(MultipartFile file, Map<String, String> headerAlias,
//                                            int headerRowIndex, int startRowIndex, Class<T> clazz) {
//        try (ExcelReader reader = ExcelUtil.getReader(file.getInputStream())) {
//            //设置标题别名的目的在于读取的excel标题是中文，一一对应上javabean中的字段。如果excel标题就是字段名，则无需设置别名
//            reader.setHeaderAlias(headerAlias).setIgnoreEmptyRow(true);
//            List<T> list = reader.read(headerRowIndex, startRowIndex, clazz);
//            for (int i = list.size() - 1; i >= 0; --i) {
//                T object = list.get(i);
//                if (BeanUtil.isNull(object)) {
//                    list.remove(i);
//                }
//            }
//            return list;
//        } catch (Exception e) {
//            LOGGER.error(e.getMessage(), e);
//        }
//
//        return new ArrayList<T>();
//    }

    public static Workbook createSingleWorkBook(Workbook wb, List<Map<String, Object>> list,
                                                String[] keys, String columnNames[]) {

        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        Row row = sheet.createRow((short) 0);

        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        Font f = wb.createFont();
        Font f2 = wb.createFont();

        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());

        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        cs.setFont(f);
        cs.setFillForegroundColor(IndexedColors.AQUA.getIndex());
//        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cs.setBorderLeft(BorderStyle.THIN);
//        cs.setBorderRight(BorderStyle.THIN);
//        cs.setBorderTop(BorderStyle.THIN);
//        cs.setBorderBottom(BorderStyle.THIN);
//        cs.setAlignment(HorizontalAlignment.CENTER);

        cs2.setFont(f2);
//        cs2.setBorderLeft(BorderStyle.THIN);
//        cs2.setBorderRight(BorderStyle.THIN);
//        cs2.setBorderTop(BorderStyle.THIN);
//        cs2.setBorderBottom(BorderStyle.THIN);
//        cs2.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        for (short i = 1; i < list.size(); i++) {
            Row row1 = sheet.createRow((short) i);
            for (short j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }

//    public static Workbook createSingleWorkBookOne(Workbook wb, List<Map<String, Object>> list) {
//
//        Sheet sheet = wb.createSheet(list.get(1).get("sheetName").toString());
//
//
//        Row row = sheet.createRow((short) 0);
//
//        CellStyle cs = wb.createCellStyle();
//        CellStyle cs2 = wb.createCellStyle();
//        CellStyle cs3 = wb.createCellStyle();
//
//        Font f = wb.createFont();
//        Font f2 = wb.createFont();
//        Font f3 = wb.createFont();
//
//        f.setFontHeightInPoints((short) 10);
//        f.setColor(IndexedColors.BLACK.getIndex());
//        f.setBold(true);
//
//        f2.setFontHeightInPoints((short) 10);
//        f2.setColor(IndexedColors.BLACK.getIndex());
//
//        f3.setFontName("simsun");
//        f3.setFontHeightInPoints((short) 20);
//        f3.setColor(IndexedColors.BLACK.getIndex());
//        f3.setBold(true);
//
//
//        cs.setFont(f);
//        cs.setFillForegroundColor(IndexedColors.AQUA.getIndex());
//        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cs.setBorderLeft(BorderStyle.THIN);
//        cs.setBorderRight(BorderStyle.THIN);
//        cs.setBorderTop(BorderStyle.THIN);
//        cs.setBorderBottom(BorderStyle.THIN);
//        cs.setAlignment(HorizontalAlignment.CENTER);
//
//        cs2.setFont(f2);
//        cs2.setBorderLeft(BorderStyle.THIN);
//        cs2.setBorderRight(BorderStyle.THIN);
//        cs2.setBorderTop(BorderStyle.THIN);
//        cs2.setBorderBottom(BorderStyle.THIN);
//        cs2.setAlignment(HorizontalAlignment.CENTER);
//
//        cs3.setFont(f3);
//        cs3.setBorderLeft(BorderStyle.THIN);
//        cs3.setBorderRight(BorderStyle.THIN);
//        cs3.setBorderTop(BorderStyle.THIN);
//        cs3.setBorderBottom(BorderStyle.THIN);
//        cs3.setAlignment(HorizontalAlignment.CENTER);
//
//        Cell cell = row.createCell(0);
//        cell.setCellValue("");
//        cell.setCellStyle(cs);
//        switch(9){
//            case 3:
//                Row row1 = sheet.createRow((short) 3);
//                for (int i=0;i<3;i++){
//                Cell cell1 = row1.createCell(3);
//                cell.setCellValue("");
//                cell.setCellStyle(cs);
//            }
//                break;
//            case 5:
//                Row row2 = sheet.createRow((short) 3);
//                for (int i=0;i<3;i++){
//                    Cell cell1 = row2.createCell(5);
//                    cell.setCellValue("");
//                    cell.setCellStyle(cs);
//                }
//            case 7:
//                Row row3 = sheet.createRow((short) 3);
//                for (int i=0;i<3;i++){
//                    Cell cell1 = row3.createCell(5);
//                    cell.setCellValue("");
//                    cell.setCellStyle(cs);
//                }
//                break;
//            case 9:
//                Row row4 = sheet.createRow((short) 3);
//                for (int i=0;i<3;i++){
//                    Cell cell1 = row4.createCell(5);
//                    cell.setCellValue("");
//                    cell.setCellStyle(cs);
//                }
//            case 11:
//                break;
//        }
////        for (short i = 2; i < list.size(); i++) {
////            Row row1 = sheet.createRow((short) i);
////            for (short j = 0; j < keys.length; j++) {
////                Cell cell = row1.createCell(j);
////                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
////                cell.setCellStyle(cs2);
////            }
////        }
//        return wb;
//    }

//    public static Workbook createSingleWorkBookTwo(Workbook wb, List<Map<String, Object>> list,
//                                                     String[] keys, String columnNames[],String columnNames2[]) {
//
//        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
//        for (int i = 0; i < keys.length; i++) {
//            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
//        }
//
//        Row row = sheet.createRow((short) 0);
//
//        CellStyle cs = wb.createCellStyle();
//        CellStyle cs2 = wb.createCellStyle();
//        CellStyle cs3 = wb.createCellStyle();
//
//        Font f = wb.createFont();
//        Font f2 = wb.createFont();
//        Font f3 = wb.createFont();
//
//        f.setFontHeightInPoints((short) 10);
//        f.setColor(IndexedColors.BLACK.getIndex());
//        f.setBold(true);
//
//        f2.setFontHeightInPoints((short) 10);
//        f2.setColor(IndexedColors.BLACK.getIndex());
//
//        f3.setFontName("simsun");
//        f3.setFontHeightInPoints((short) 20);
//        f3.setColor(IndexedColors.BLACK.getIndex());
//        f3.setBold(true);
//
//
//        cs.setFont(f);
//        cs.setFillForegroundColor(IndexedColors.AQUA.getIndex());
//        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cs.setBorderLeft(BorderStyle.THIN);
//        cs.setBorderRight(BorderStyle.THIN);
//        cs.setBorderTop(BorderStyle.THIN);
//        cs.setBorderBottom(BorderStyle.THIN);
//        cs.setAlignment(HorizontalAlignment.CENTER);
//
//        cs2.setFont(f2);
//        cs2.setBorderLeft(BorderStyle.THIN);
//        cs2.setBorderRight(BorderStyle.THIN);
//        cs2.setBorderTop(BorderStyle.THIN);
//        cs2.setBorderBottom(BorderStyle.THIN);
//        cs2.setAlignment(HorizontalAlignment.CENTER);
//
//        cs3.setFont(f3);
//        cs3.setBorderLeft(BorderStyle.THIN);
//        cs3.setBorderRight(BorderStyle.THIN);
//        cs3.setBorderTop(BorderStyle.THIN);
//        cs3.setBorderBottom(BorderStyle.THIN);
//        cs3.setAlignment(HorizontalAlignment.CENTER);
//
//        Cell cell0 = row.createCell(0);
//        cell0.setCellValue(columnNames2[0]);
//        cell0.setCellStyle(cs);
//        for (int i = 1; i < columnNames.length; i++) {
//            Cell cell = row.createCell(i);
//            cell.setCellValue(columnNames[i]);
//            cell.setCellStyle(cs);
//        }
//        for (short i = 2; i < list.size(); i++) {
//            Row row1 = sheet.createRow((short) i);
//            for (short j = 0; j < keys.length; j++) {
//                Cell cell = row1.createCell(j);
//                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
//                cell.setCellStyle(cs2);
//            }
//        }
//        return wb;
//    }

    public static void ExcelOutputStream(List<List<Map<String, Object>>> list, List<String[]> keys,
                                         List<String[]> columnNames, String fileName,
                                         HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            Workbook wb = new HSSFWorkbook();
            for (int i = 0; i < list.size(); i++) {
                wb = createSingleWorkBook(wb, list.get(i), keys.get(i), columnNames.get(i));
            }
            wb.write(os);
        } catch (IOException e) {
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    public static void ExcelSingleOutputStream(List<Map<String, Object>> list, String[] keys, String[] columnNames,
                                               String fileName, HttpServletRequest request, HttpServletResponse response) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            createSingleWorkBook(new HSSFWorkbook(), list, keys, columnNames).write(os);
        } catch (IOException e) {
        }
        ExcelOutputStream(fileName, request, response, os);
    }

    private static void ExcelOutputStream(String fileName, HttpServletRequest request, HttpServletResponse response, ByteArrayOutputStream os) {
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);

        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes(), "ISO-8859-1") + ".xls");
        } catch (UnsupportedEncodingException e) {
        }
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
        } catch (IOException e) {
        }
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;

        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
        } finally {
            if (bis != null)
                try {
                    bis.close();
                } catch (IOException e) {
                }
            if (bos != null)
                try {
                    bos.close();
                } catch (IOException e) {
                }
        }
    }

//    public static <T> Workbook createWorkBook(List<T> resultList, Class<? extends ExcelCell> clazz) throws IllegalAccessException {
//        Workbook wb = new HSSFWorkbook();
//
//        Sheet sheet = wb.createSheet();
//
//        ExcelCell[] columns = clazz.getEnumConstants();
//
//        Row row = sheet.createRow(0);
//
//        for (ExcelCell column : columns) {
//            sheet.setColumnWidth(column.getColumn(), 256 * column.getColumnLength() + 184);
//            row.createCell(column.getColumn()).setCellValue(column.getColumnInfo());
//        }
//
//        for (int i = 0; i < resultList.size(); i++) {
//            T obj = resultList.get(i);
//
//            Field[] fields = obj.getClass().getDeclaredFields();
//
//            Row contentRow = sheet.createRow(i + 1);
//            for (int j = 0; j < columns.length; j++) {
//                ExcelCell column = columns[j];
//
//                Field field = fields[j];
//                field.setAccessible(true);
//
//                contentRow.createCell(column.getColumn()).setCellValue(field.get(obj).toString());
//            }
//        }
//
//        return wb;
//    }


    public static List<SkillDTO> getAllByExcel(MultipartFile file, Integer sheetIndex, Integer StartReadLine ) {
        try {
            // 同时支持Excel 2003、2007
            FileInputStream is = new FileInputStream(multipartFileToFile(file)); // 文件流
            Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel
            List<SkillDTO> list = readExcel(workbook, sheetIndex, StartReadLine, 0);
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  new ArrayList();
    }
    public static File multipartFileToFile(MultipartFile file) throws Exception {

        File toFile = null;
        if (file.equals("") || file.getSize() <= 0) {
            file = null;
        } else {
            InputStream ins = null;
            ins = file.getInputStream();
            toFile = new File(file.getOriginalFilename());
            inputStreamToFile(ins, toFile);
            ins.close();
        }
        return toFile;
    }

    //获取流文件
    private static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除本地临时文件
     * @param file
     */
    public static void delteTempFile(File file) {
        if (file != null) {
            File del = new File(file.toURI());
            del.delete();
        }
    }


//    public static  String uploadExeSkill(MultipartFile file, Integer skillId) {
//        if (file == null) return null;
//
//        String fileName = file.getOriginalFilename();
//        String suffix = StrUtil.subAfter(fileName, ".", true);
//        String sufbix = StrUtil.subBefore(fileName, ".", true);
//        String uuid="GW-" + String.format("%05d", skillId);
//        String newFileNameRemoveSuffix =
//                StringUtils.join(sufbix,uuid,"." ,suffix);
//
//        String filePath = LocalResourcesEnum.SYSTEM_FILE.getLocalUploadPath(newFileNameRemoveSuffix);
//        // 创建文件目录
//        if (new File(filePath).exists()) {
//            FileUtil.del(filePath);
//        }
//
//        // 写入文件
//        File newFile = new File(filePath);
//        try {
//            file.transferTo(newFile);
//        } catch (IOException e) {
//            System.out.println(e.getMessage());
//            throw new GlobalException("Excel上传失败");
//        }
//
//        return newFileNameRemoveSuffix;
//    }

    private static List<SkillDTO> readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) throws Exception {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        String[] res = new String[sheet.getLastRowNum() - tailLine + 1];
        List list= Lists.newArrayList();
try {


        for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {

            row = sheet.getRow(i);
            res[i] = "";
            SkillDTO skillDTO=new SkillDTO();
            int count = 0;
            for (Cell c : row) {
                count++;
//                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
//                // 判断是否具有合并单元格
//
//                if (isMerge) {
//                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
//
//                    //System.out.print(rs + "_"+ row.getRowNum()+"_"+c.getColumnIndex() +"_");
//                    res[i] = rs;
//                } else {
//                    //System.out.print(c.getRichStringCellValue() + "");
//                    res[i] = getCellValue(c);
//                }
                res[i] = getCellValue(c);
                switch (count){
                    case 1: // 岗位
                        skillDTO.setPost(res[i]);
                    if ("".equals(res[i])&&i==1) return new ArrayList<>();
                        break;
                    case 2: // 描述
                        skillDTO.setDescribe(res[i]);
                        break;
                    case 3: // 技能模块
                        skillDTO.setSkillModule(replaceBlank(res[i]));
                        break;
                    case 4: // 子技能
                        skillDTO.setSubSkill(replaceBlank(res[i]));
                        break;
                    case 5: // 技能点
                        skillDTO.setKnowledgePoints(replaceBlank(res[i]));
                        break;
                    case 6: // 课程名称
                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                // 判断是否具有合并单元格

                if (isMerge) {
                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());

                    //System.out.print(rs + "_"+ row.getRowNum()+"_"+c.getColumnIndex() +"_");
                    res[i] = rs;
                } else {
                    //System.out.print(c.getRichStringCellValue() + "");
                    res[i] = getCellValue(c);
                }
                        skillDTO.setCourseName(replaceBlank(res[i]));
                        break;
                }
                if (count==7) {
                    if (!"".equals(res[i])) return new ArrayList<>();
                break;}
            }
            list.add(skillDTO);

        }
        return  list; }
        catch (Exception e){
            throw new Exception();
}

    }
    private static String replaceBlank(String str) {
        String dest = "";
        if (str!=null) {
            Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(str);
            dest = m.replaceAll("");
        }
        return dest;
    }
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }

    private static String getCellValue(Cell cell) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        String cellValue = "";
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_STRING: // 文本
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                } else {
                    cellValue = String.valueOf((int) cell.getNumericCellValue()); // 数字
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: // 空白
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: // 错误
                cellValue = "错误";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = "错误";
                break;
            default:
                cellValue = "错误";
        }
        return cellValue;
    }
}
